<?php
class ModelToolSaleImport extends Model {
	
	//上传订单文件
	public function uploadOrder($filename,$check){

		global $registry;
		$registry = $this->registry;
		set_error_handler('error_handler_for_export',E_ALL);
		register_shutdown_function('fatal_error_shutdown_handler_for_export');

		$cwd = getcwd();
		chdir( DIR_SYSTEM.'PHPExcel' );
		require_once( 'Classes/PHPExcel.php' );
		chdir( $cwd );

		$inputFileType = PHPExcel_IOFactory::identify($filename);
		$objReader = PHPExcel_IOFactory::createReader($inputFileType);
		$objReader->setReadDataOnly(true);
		$reader = $objReader->load($filename);

		$excel = $reader->getSheet(0);
		$data = array();
		$isFirstRow = TRUE;
		$i = 0;
		$k = $excel->getHighestRow();
		for ($i=0; $i<$k; $i+=1){

			$j = 1;
			if ($isFirstRow) {
				$isFirstRow = FALSE;
				continue;
			}

			$group = trim($this->getCell($excel,$i,$j++));
			$telephone = trim($this->getCell($excel,$i,$j++));
			$product_name = trim($this->getCell($excel,$i,$j++));
			$product_code = trim($this->getCell($excel,$i,$j++));
			$price = trim($this->getCell($excel,$i,$j++));
			$quantity = trim($this->getCell($excel,$i,$j++));
			$total = trim($this->getCell($excel,$i,$j++));
			$time = trim($this->getCell($excel,$i,$j++));
			$receive = trim($this->getCell($excel,$i,$j++));
			$address = trim($this->getCell($excel,$i,$j++));
			$recommend_usr_id = trim($this->getCell($excel,$i,$j++));

			$telephone =str_replace('-','',$telephone);

			if(strlen($product_code) == 10){
				$product_code = $product_code."0";
			}
			
			$quantity = round($quantity,0);
			
			if($time != null){
				$dateAdded = ($time-25569)*24*60*60 - 8*60*60;
				$dateAdded = date('Y-m-d H:i', $dateAdded); 
			}
			else{
				$dateAdded = date('Y-m-d H:i');
			}
			
			//价格为空取系统价格
			if($total == null || $check['importPrice'] == 1){

				$post_date = date('Ymd', strtotime($dateAdded));
				$post_pid = $this->getProductId($product_code);
				$post_cgroup = $this->getCustomerGroup($telephone);
				$this->load->model('catalog/mvd_product');
				$temp_total = $quantity*($this->model_catalog_mvd_product->getSpecialPrice($post_pid,$post_cgroup,$post_date));
				if($temp_total == 0){
					$total = $quantity*($this->getSalePrice($post_pid));
				}
				else{
					$total = $temp_total;
				}
			
			}
			
			$data[] = array(
				'group' => $group.$telephone,
				'telephone' => $telephone,
				'product_code' => $product_code,
				'quantity' => $quantity,
				'total' => $total,
				'receive' => $receive,
				'address' => $address,
				'date_added' => $dateAdded,
				'recommend_usr_id' => $recommend_usr_id
			);

		}
		
		//order_product数组
		$order_product = array();
		foreach($data as $key => $v){
			
			$order_product[$v['group']][] = $v;
			$order_product[$v['group']]['order_total'] += $v['total'];
			$order_product[$v['group']]['telephone'] = $v['telephone'];
			$order_product[$v['group']]['date_added'] = $v['date_added'];
			$order_product[$v['group']]['receive'] = $v['receive'];
			$order_product[$v['group']]['address'] = $v['address'];
			$order_product[$v['group']]['recommend_usr_id'] = $v['recommend_usr_id'];

		}
		
		//order单据数组
		$order = array();
		foreach($order_product as $key => $v){
			
			$order[] = array(
				'group' => $key,
				'telephone' => $v['telephone'],
				'total' => $v['order_total'],
				'receive' => $v['receive'],
				'address' => $v['address'],
				'recommend_usr_id' => $v['recommend_usr_id'],
				'date_added' => $v['date_added']
			);

		}

		return $this->createOrder($order,$order_product,$check['importStock']);

	}
	
	//生成订单相关单据
	public function createOrder($order,$order_product,$isstock){
		
		if(count($order) > 0){
			 return $this->addOrder($order,$order_product,$isstock);
		}

	}
	
	//导入order表,同时生成order_product,order_total,order_history
	public function addOrder($order,$order_product,$isstock){
		
		$ok = true;
		$user_id = 160;
		foreach($order as $v){

			if($v['total'] == 0){ //判断总价为0不导入
				continue;
			}
			
			//获取customer信息
			$customer = $this->telToCustomer($v);
			
			$add['id_code'] = "";
			$add['invoice_prefix'] = "";
			$add['store_name'] = '百货栈';
			$add['store_url'] = 'import'.date('Y-m-d');

			$add['customer_id'] = $customer['customer_id'];
			$add['customer_group_id'] = $customer['customer_group_id'];
			$add['fullname'] = $customer['fullname'];
			$add['email'] = $customer['email'];
			$add['telephone'] = $customer['telephone'];
			$add['fax'] = $customer['fax'];
			$add['custom_field'] = $customer['custom_field'];
			
			$add['payment_fullname'] = ($customer['pfullname'] != null) ? $customer['pfullname'] : '导入时暂无数据';	
			$add['payment_company'] = ($customer['pcompany'] != null) ? $customer['pcompany'] : '导入时暂无数据';	
			$add['payment_address'] = ($customer['paddress'] != null) ? $customer['paddress'] : '导入时暂无数据';			
			$add['payment_city'] = ($customer['city_name'] != null) ? $customer['city_name'] : '导入时暂无数据';
			$add['payment_city_id'] = ($customer['city_id'] != null) ? $customer['city_id'] : 0;
			$add['payment_postcode'] = ($customer['ppostcode'] != null) ? $customer['ppostcode'] : '导入时暂无数据';
			$add['payment_country'] = ($customer['country_name'] != null) ? $customer['country_name'] : '导入时暂无数据';
			$add['payment_country_id'] = ($customer['country_id'] != null) ? $customer['country_id'] : 0;
			$add['payment_zone'] = ($customer['zone_name'] != null) ? $customer['zone_name'] : '导入时暂无数据';
			$add['payment_zone_id'] = ($customer['zone_id'] != null) ? $customer['zone_id'] : 0;
			$add['payment_address_format'] = "";
			$add['payment_custom_field'] = "";
			$add['payment_method'] = '货到付款';
			$add['payment_code'] = 'cod';

			$add['shipping_fullname'] = $add['payment_fullname'];	
			$add['shipping_company'] = $add['payment_company'];	
			$add['shipping_address'] = $add['payment_address'];				
			$add['shipping_city'] = $add['payment_city'];
			$add['shipping_city_id'] = $add['payment_city_id'];
			$add['shipping_postcode'] = $add['payment_postcode'];
			$add['shipping_country'] = $add['payment_country'];
			$add['shipping_country_id'] = $add['payment_country_id'];
			$add['shipping_zone'] = $add['payment_zone'];
			$add['shipping_zone_id'] = $add['payment_zone_id'];
			$add['shipping_address_format'] = "";
			$add['shipping_custom_field'] = "";
			$add['shipping_method'] = '免运费';
			$add['shipping_code'] = 'free.free';
			$add['shipping_telephone'] = ($customer['ptelephone'] != null) ? $customer['ptelephone'] : '导入时暂无数据';
			
			$add['comment'] = "";
			$add['total'] = $v['total'];
			$add['ori_total'] = $v['total'];
			$add['affiliate_id'] = 0;
			$add['commission'] = 0;
			$add['marketing_id'] = 0;
			$add['tracking'] = "";
			$add['order_status_id'] = 5;
			$add['language_id'] = 1;
			$add['currency_id'] = 4;
			$add['currency_code'] = 'CNY';
			$add['ip'] = $customer['ip'];
			$add['forwarded_ip'] = "";
			$add['user_agent'] = 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/61.0.3163.100 Safari/537.36';
			$add['accept_language'] = 'zh-CN,zh;q=0.8';
			$add['date_added'] = $v['date_added'];
			$add['date_modified'] = $v['date_added'];
			$add['logcenter_id'] = $customer['logcenter_id'];
			$add['is_pay'] = 1;
			$add['recommend_usr_id'] = $customer['user_id'];
			
			$add['yijipay_tradeNo'] = "";
			$add['paid_time'] = $v['date_added'];
			$add['auditted_time'] = date('Y-m-d H:i',strtotime('+1 d',strtotime($v['date_added'])));
			$add['delivered_time'] = date('Y-m-d H:i',strtotime('+2 d',strtotime($v['date_added'])));
			$add['completed_time'] = date('Y-m-d H:i',strtotime('+3 d',strtotime($v['date_added'])));
			$add['out_time'] = date('Y-m-d H:i',strtotime('+2 d',strtotime($v['date_added'])));
			$add['hang_status'] = 0;
			$add['verify_status'] = 2;
			$add['first_stock_out_rate'] = 100;
			$add['final_stock_out_rate'] = 100;
			$add['total_percent'] = 100;

			$oid = M('order')->data($add)->add();
			if($oid){

				$this->addOrderProduct($oid,$user_id,$v['total'],$add['auditted_time'],$order_product[$v['group']],$isstock); //订单产品
				$this->addOrderTotal($oid,$v['total']); //订单总计
				$this->addOrderHistory($oid,$user_id); //订单历史

			}
			else{
				$ok = false;
			}

		}

		return $ok;

	}
	
	//导入order_total表
	public function addOrderTotal($oid,$total){
		
		for($i=1;$i<3;$i++){

			$add['order_id'] = $oid;
			if($i == 1){
				$add['code'] = 'sub_total';
				$add['title'] = '小计';
			}
			else{
				$add['code'] = 'total';
				$add['title'] = '总计';
			}
			$add['value'] = $total;
			$add['sort_order'] = $i;

			M('order_total')->data($add)->add();
		
		}

	}
	
	//导入order_history表
	public function addOrderHistory($oid,$user_id){

		$username = $this->getUserName($user_id);

		$add['order_id'] = $oid;
		$add['order_status_id'] = 5;
		$add['notify'] = 0;
		$add['comment'] = '后台用户: '.$username.', 上传了文件导入了此外场订单';
		$add['date_added'] = date('Y-m-d H:i');
		$add['vendor_id'] = 0;

		M('order_history')->data($add)->add();

	}
	
	//导入order_product表
	public function addOrderProduct($oid,$user_id,$allTotal,$date_added,$order_product,$isstock){
		
		if($isstock == 1){
			$out_id = $this->addStockOut($oid,$user_id,$allTotal,$date_added); //生成出库单
			$this->addStockOutHistory($out_id,$user_id); //出库单历史
		}

		foreach($order_product as $v){

			if($v['quantity']==0){ //判断总价为0不导入
				continue;
			}

			$product = $this->getProduct($v['product_code']);

			if($product != null){
			
				$add['order_id'] = $oid;
				$add['product_id'] = $product['product_id'];
				$add['product_code'] = $product['product_code'];
				$add['name'] = $product['name'];
				$add['model'] = $product['model'];

				$add['quantity'] = $v['quantity'];
				$add['price'] = $v['total']/$v['quantity'];
				$add['pay_price'] = $add['price'];
				$add['total'] = $v['total'];
				$add['pay_total'] = $v['total'];
				$add['reward'] = 0;
				$add['vendor_id'] = 0;
				$add['title'] = "";
				$add['shipped_time'] = date('Y-m-d H:i');
				$add['lack_quantity'] = 0;
				$add['order_status_id'] = 5;

				$order_product_id = M('order_product')->data($add)->add();	

				$groupData = array(
					
					'user_id' => $user_id,
					'out_id' => $out_id,
					'product_id' => $add['product_id'],
					'quantity' => $add['quantity'],
					'total' => $add['total'],
					'date_added' => $date_added

				);

				if($product['ptype'] == 2){ //判断是否组合商品
					$this->addProductGroup($order_product_id,$groupData,$isstock); //组合商品
				}
				else if($isstock == 1){
					$add['user_id'] = $user_id;
					$this->addOutDetail($out_id,$add,$date_added); //出库商品
				}

			}
			else{
				continue;
			}

		}

	}
	
	//导入订单商品组合
	public function addProductGroup($opid,$gData,$isstock){
		
		$where['parent_product_id'] = $gData['product_id'];
		$pgroup = M('product_group')->where($where)->select();
		$allcount = $gData['quantity']*(M('product_group')->where($where)->getField('sum(child_quantity) as sum'));
		$dividePrice = $gData['total']/$allcount;

		foreach($pgroup as $pg){
			
			$add['order_product_id'] = $opid;
			$add['product_id'] = $pg['child_product_id'];
			$add['product_code'] = $pg['child_product_code'];
			$add['quantity'] = $gData['quantity']*$pg['child_quantity'];
			$add['price'] = $dividePrice;
			$add['pay_price'] = $dividePrice;
			$add['total'] = $add['quantity']*$dividePrice;
			$add['pay_total'] = $add['total'];
			$add['lack_quantity'] = 0;

			$opgId = M('order_product_group')->data($add)->add();
			
			if($opgId && $isstock == 1){

				$aod = array(
					
					'user_id' => $gData['user_id'],
					'product_code' => $add['product_code'],
					'quantity' => $add['quantity'],
					'price' => $add['price'],
					'total' => $add['total']	
				
				);
				
				$this->addOutDetail($gData['out_id'],$aod,$gData['date_added']); //出库商品

			}

		}

	}
	
	//导入出库单
	public function addStockOut($oid,$user_id,$allTotal,$date_added){
		
		$add['refer_id'] = $oid;
		$add['refer_type_id'] = 1;
		$add['warehouse_id'] = 10;
		$add['user_id'] = $user_id;
		$add['sale_money'] = $allTotal;
		$add['pay_sale_money'] = $allTotal;
		$add['status'] = 2;
		$add['out_date'] = $date_added;
		$add['date_added'] = $date_added;

		$stock_out = M('stock_out')->data($add)->add();

		return $stock_out;

	}
	
	//导入stock_out_history表
	public function addStockOutHistory($out_id,$user_id){

		$username = $this->getUserName($user_id);

		$add['out_id'] = $out_id;
		$add['user_id'] = $user_id;
		$add['operator_name'] = $username;
		$add['notify'] = 0;
		$add['comment'] = '后台用户: '.$username.', 上传了文件生成了此外场出库单';
		$add['date_added'] = date('Y-m-d H:i');

		M('stock_out_history')->data($add)->add();

	}
	
	//导入出库单详细
	public function addOutDetail($out_id,$data,$date_added){

		$add['out_id'] = $out_id;
		$add['product_code'] = $data['product_code'];
		$add['product_quantity'] = $data['quantity'];
		$add['product_price'] = $data['price'];
		$add['pay_product_price'] = $add['product_price'];
		$add['products_money'] = $data['total'];
		$add['pay_products_money'] = $add['products_money'];
		$add['date_added'] = $date_added;

		$sodId = M('stock_out_detail')->data($add)->add();
		
		if($sodId){

			$inventoryData = array(
				'user_id' => $data['user_id'],
				'out_id' => $add['out_id'],
				'warehouse_id' => 10,
				'product_code' => $add['product_code'],
				'change' => $add['product_quantity']

			);
			$this->changeInventory($inventoryData); //改变库存

		}

	}
	
	//改变库存
	public function changeInventory($iData){
		
		$where['warehouse_id'] = $iData['warehouse_id'];
		$where['product_code'] = $iData['product_code'];
		$save['date_added'] = date('Y-m-d H:i');
		$inventory_id = M('inventory')->where($where)->getField('id');
		// var_dump($inventory_id);die();

		M('inventory')->where($where)->setDec('account_quantity',$iData['change']);
		M('inventory')->where($where)->setDec('available_quantity',$iData['change']);
		M('inventory')->where($where)->save($save);
		
		$iData['inventory_id'] = $inventory_id;
		$this->addInventoryHistory($iData); //库存改变历史记录

	}
	
	//库存改变历史记录
	public function addInventoryHistory($iData){
		
		$add['inventory_id'] = $iData['inventory_id'];
		$add['product_code'] = $iData['product_code'];
		$add['warehouse_id'] = $iData['warehouse_id'];
		$add['account_qty'] = "-".$iData['change'];
		$add['available_qty'] = $add['account_qty'];
		$add['comment'] = "出库单: ".$iData['out_id']."完成出库";
		$add['user_id'] = $iData['user_id'];
		$add['date_added'] = date('Y-m-d H:i');
		if (!empty($iData['inventory_id'])) {
			M('inventory_history')->data($add)->add();
			
		}

	}
	
	//获取商品信息
	public function getProduct($product_code){
		
		$where['pov.product_code'] = $product_code;
		$product = M('product')
			->alias('p')
			->join('product_description pd on p.product_id = pd.product_id','left')
			->join('product_option_value pov on p.product_id = pov.product_id','left')
			->where($where)
			->field('p.product_id as product_id,pov.product_code as product_code,pd.name as name,p.model as model,p.product_type as ptype')
			->select();

		return $product[0];

	}
	
	//获得用户姓名
	public function getUserName($user_id){
		
		$where['user_id'] = $user_id;
		$name = M('user')->where($where)->getField('username');

		return $name;

	}
	
	//电话获取customer信息
	public function telToCustomer($data){

		$newtel = $this->checkCustomer($data);
		
		$where['cu.telephone'] = $newtel;
		$customer = M('customer')
			->alias('cu')
			->join('address ad on ad.address_id = cu.address_id','left')
			->join('city ci on ci.city_id = ad.city_id','left')
			->join('country co on co.country_id = ad.country_id','left')
			->join('zone zo on zo.zone_id = ad.zone_id','left')
			->where($where)
			->field('cu.*,ad.fullname as pfullname,ad.company as pcompany,ad.address as paddress,ad.postcode as ppostcode,ci.name as city_name,ad.city_id as city_id,co.name as country_name,ad.country_id as country_id,zo.name as zone_name,ad.zone_id as zone_id,ad.shipping_telephone as ptelephone')
			->select();

		return $customer[0];

	}
	
	//检查用户是否存在,若不存在
	public function checkCustomer($data){
		
		$where['telephone'] = $data['telephone'];
		$customer = M('customer')->where($where)->select();
		
		if($customer){
			return $customer[0]['telephone'];
		}
		else{
			$newtel = $this->addCustomerTel($data);
			return $newtel;
		}

	}
	
	//导入手机添加用户
	public function addCustomerTel($data){
		
		//获取推荐码
		$userwhere['user_id'] = $data['recommend_usr_id'];
		$recommended_code = M('user')->where($userwhere)->getField('recommended_code');

		$salt = substr(md5(uniqid(rand(),true)),0,9);
		$add = array(
			'customer_group_id' => 1,
			'store_id' => 0,
			'send_new' => 0,
			'fullname' => $data['telephone'],
			'email' => $data['telephone']."@.com",
			'telephone' => $data['telephone'],
			'fax' => "",
			'custom_field' => "",
			'ip' => "",
			'approved' => 1,
			'safe' => 0,
			'token' => "",
			'yiji_userid' => "",
			'password' => $this->db->escape(sha1($salt.sha1($salt.sha1('123456')))),
			'salt' => $salt,
			'user_id' => $data['recommend_usr_id'],
			'recommended_code' => $recommended_code,
			'status' => 1,
			'date_added' => date('Y-m-d H:i'),
			'logcenter_id' => 10,
		);
		
		$customer_id = M('customer')->data($add)->add();
		
		$address['customer_id'] = $customer_id;
		$address['fullname'] = $data['receive'];
		$address['company'] = $data['receive'];
		$address['address'] = $data['address'];
		$address['city'] = "";
		$address['postcode'] = "";
		$address['shipping_telephone'] = $data['telephone'];
		$address['custom_field'] = "";
		$address['need_review'] = 0;

		$default['address_id'] = M('address')->data($address)->add();
		M('customer')->where('customer_id='.$customer_id)->save($default);

		return $data['telephone'];

	}

	//获得商品id
	public function getProductId($product_code){
		
		$where['product_code'] = $product_code;
		$pid = M('product_option_value')->where($where)->getField('product_id');

		return $pid;

	}

	//获得customer_group_id
	public function getCustomerGroup($tel){
		
		$where['telephone'] = $tel;
		$cgid = M('customer')->where($where)->getField('customer_group_id');

		return $cgid;

	}

	//获得sale_price
	public function getSalePrice($pid){
		
		$where['product_id'] = $pid;
		$sp = M('product')->where($where)->getField('sale_price');

		return $sp;

	}
	
	//获取excel数据
	public function getCell(&$worksheet,$row,$col,$default_val=''){

		$col -= 1;
		$row += 1;
		return ($worksheet->cellExistsByColumnAndRow($col,$row)) ? $worksheet->getCellByColumnAndRow($col,$row)->getCalculatedValue() : $default_val; //获取列值包括公式计算

	}

}
?>